package com.gm.wj.dao;

import com.gm.wj.entity.SysFlow;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

public interface SysFlowDAO extends JpaRepository<SysFlow,Integer> {
    @Query(value = "select count(1) from sys_flow where trade_no = ?1 ",nativeQuery = true)
    int findByTradeNo(String trade_no);

    SysFlow findByOrderId(String orderId);

    // jpa分页查询  多条件
    Page<SysFlow> findAll(Specification<SysFlow> sysStudentSpecification, Pageable pageable);

    // 课程售出数量折线图
    @Query(value = "select a.click_date as days,ifnull(b.count,0) as count \n" +
            " from (\n" +
            "    SELECT curdate() as click_date\n" +
            "    union all\n" +
            "    SELECT date_sub(curdate(), interval 1 day) as click_date\n" +
            "    union all\n" +
            "    SELECT date_sub(curdate(), interval 2 day) as click_date\n" +
            "    union all\n" +
            "    SELECT date_sub(curdate(), interval 3 day) as click_date\n" +
            "    union all\n" +
            "    SELECT date_sub(curdate(), interval 4 day) as click_date\n" +
            "    union all\n" +
            "    SELECT date_sub(curdate(), interval 5 day) as click_date\n" +
            "    union all\n" +
            "    SELECT date_sub(curdate(), interval 6 day) as click_date\n" +
            ") a left join (\n" +
            "  select date(create_time) as datetime, count(*) as count\n" +
            "  from sys_order WHERE order_status = 1\n" +
            "  group by date(create_time)\n" +
            ") b on a.click_date = b.datetime \n" +
            " ORDER BY days asc; ",nativeQuery = true)
    List<Object> findFlowByBuy();

    int countByIsPay(int i);

    @Query(value = "select a.click_date as days,ifnull(b.count,0) as count\n" +
            "from (\n" +
            "    SELECT curdate() as click_date\n" +
            "    union all\n" +
            "    SELECT date_sub(curdate(), interval 1 day) as click_date\n" +
            "    union all\n" +
            "    SELECT date_sub(curdate(), interval 2 day) as click_date\n" +
            "    union all\n" +
            "    SELECT date_sub(curdate(), interval 3 day) as click_date\n" +
            "    union all\n" +
            "    SELECT date_sub(curdate(), interval 4 day) as click_date\n" +
            "    union all\n" +
            "    SELECT date_sub(curdate(), interval 5 day) as click_date\n" +
            "    union all\n" +
            "    SELECT date_sub(curdate(), interval 6 day) as click_date\n" +
            ") a left join (\n" +
            "  select date(create_time) as datetime, SUM(pay_amount) as count\n" +
            "  from sys_flow WHERE is_pay != 1 \n" +
            "  group by date(create_time)\n" +
            ") b on a.click_date = b.datetime \n" +
            " ORDER BY days asc; ",nativeQuery = true)
    List<Object> findFlowByMoney();

}
